import pymysql, json


class ConnMysql():

    def __init__(self, host, user, password, database,port):
        try:
            con = pymysql.connect(
                host=host,
                user=user,
                password=password,
                database=database,
                port=port
            )
            self.con = con
        except Exception as e:

            print("连接失败", e.args)

    def select_tb(self, sql):
        try:
            self.cursor = self.con.cursor()
            self.cursor.execute(sql)
            self.con.commit()
            result = self.cursor.fetchall()
            # print('result类型',type(result))    result = cursor.fetchall()类型为元祖
            # print("查询到的结果:", json.dumps(result,ensure_ascii=False))
            return json.dumps(result, ensure_ascii=True)
        except Exception as e:
            print("查询失败", e.args)

    def update_tb(self, sql):
        try:
            self.cursor = self.con.cursor()
            self.cursor.execute(sql)
            self.con.commit()
        except Exception as e:
            print("更新失败", e.args)
            self.con.rollback()

    def del_tb(self, sql):
        try:
            self.cursor = self.con.cursor()
            self.cursor.execute(sql)
            self.con.commit()
        except Exception as e:
            print("删除失败", e.args)
            self.con.rollback()

    def __del__(self):
        # 这是对象销毁前执行的方法
        self.cursor = self.con.cursor()
        self.cursor.close()
        self.con.close()


if __name__ == '__main__':
    # sql = 'select worker_sn from base_worker_info where del_flag=0'
    # print(ConnMysql().select_tb(sql))#类型为str
    #
    # sql2 = "select u_id from base_worker_info where id in (select relation_id from base_group_relationship)"
    #
    #
    # uuid_list = []
    # for item in json.loads(ConnMysql().select_tb(sql2)):
    #
    #     uuid_list.append(item[0])
    # print(uuid_list)

    # sql = "delete from attend_raw_image where worker_uid ='C0CBADCEB87B40E696521554D1ACA7FD';"
    #ConnMysql().del_tb(sql)

    # sql3 = "select u_id from base_worker_info where del_flag=0 and depart_id= '901353161301721088'"
    # uuid_list = []
    # for index,item in enumerate(json.loads(ConnMysql().select_tb(sql3))):
    #     uuid_list.append({'u_id':item[0],'index':index+1})
    # print(uuid_list)

    # sql = "select id,name from base_depart_info where del_flag=0 and id ='943755880830394368'"
    # departIdArr = json.loads(ConnMysql().select_tb(sql))

    # sql2 = "select u_id from base_worker_info where del_flag=0"
    #
    # uuidList = [item[0] for item in json.loads(ConnMysql('192.168.30.81', 'root', '123456', 'xg').select_tb(sql2))]
    # print(uuidList)
    conn = ConnMysql('192.168.30.81', 'shxirs', 'CdG~gfMj3H', 'irs','6767')

